package com.gdpu.project.util.excel;

import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.struts2.ServletActionContext;

import com.gdpu.page.vo.QueryResult;
import com.gdpu.project.service.ChengGuoService;
import com.gdpu.project.util.DateUtil;
import com.gdpu.project.vo.CgConference;
import com.gdpu.project.vo.CgInnovate;
import com.gdpu.project.vo.CgPatent;
import com.gdpu.project.vo.CgProject;
import com.gdpu.project.vo.CgThesis;
import com.gdpu.project.vo.CgTreatise;
import com.gdpu.project.vo.Teacher;
import com.opensymphony.xwork2.ActionSupport;

public class ExcelOption extends ActionSupport{
	private String department;//教研室
	private String college;   //学院
	private String postion;    //职称
	private String education;	//学历
	private String achievementType;		//成果类型
	private String cataloge;	//系名
	private String startTime;	//开始时间
	private String endTime;		//结束时间
	private String teacherId;	//教师编号
	private ChengGuoService chengguoService;	//成果类型
	private String path = ServletActionContext.getServletContext().getRealPath("/WEB-INF/download");
	private File file = new File(path+File.separator+"result.xls");
	private String realpath;
	
	
	public void setRealpath(String realpath) {
		this.realpath = realpath;
	}
	public String getRealpath(){
		return file.getAbsolutePath();
	}
	public String getDepartment() {
		return department;
	}
	public void setDepartment(String department) {
		this.department = department;
	}
	public ChengGuoService getChengguoService() {
		return chengguoService;
	}

	public void setChengguoService(ChengGuoService chengguoService) {
		this.chengguoService = chengguoService;
	}
	public String getTeacherId() {
		return teacherId;
	}
	public void setTeacherId(String teacherId) {
		this.teacherId = teacherId;
	}
	public String getCollege() {
		return college;
	}
	public void setCollege(String college) {
		this.college = college;
	}
	
	public String getPostion() {
		return postion;
	}
	public void setPostion(String postion) {
		this.postion = postion;
	}
	public String getEducation() {
		return education;
	}
	public void setEducation(String education) {
		this.education = education;
	}
	public String getAchievementType() {
		return achievementType;
	}
	public void setAchievementType(String achievementType) {
		this.achievementType = achievementType;
	}
	public String getCataloge() {
		return cataloge;
	}
	public void setCataloge(String cataloge) {
		this.cataloge = cataloge;
	}
	public String getStartTime() {
		return startTime;
	}
	public void setStartTime(String startTime) {
		this.startTime = startTime;
	}
	public String getEndTime() {
		return endTime;
	}
	public void setEndTime(String endTime) {
		this.endTime = endTime;
	}
	
	public void WritExcel(){		
		int count = 1;//写入的开始行
		try {
			WritableWorkbook book = Workbook.createWorkbook(file);
			
			WritableSheet sheet = book.createSheet("第一页", 0);			
			
			/**
			 * 科研项目
			 */
			if(achievementType.equals("kyxm")){
				QueryResult<CgProject> queryResult = chengguoService.searchCgProjectByDate(college, department, postion, education,
						teacherId, cataloge, DateUtil.stringToDate(getStartTime()), DateUtil.stringToDate(getEndTime()),-1,-1);
				List<CgProject> cgProjectlist =queryResult.getResultlist();
				String[] title = {"序号","项目负责人","合作单位","立项时间","项目来源","项目名称","经费（万元）","总分值",
						"科研分酬金（每分折算4.6元，单位：元）","奖励酬金（元）","备注"};
				for(int i=0;i<title.length;i++){
					Label label = new Label(i,0,title[i]);
					sheet.addCell(label);
				}
				Iterator<CgProject> cgProjectIterator = cgProjectlist.iterator();
				while(cgProjectIterator.hasNext()){ 
					CgProject cgProject = cgProjectIterator.next();
					String[] content = {Integer.toString(count),cgProject.getFuZeRen(),cgProject.getCooEnterprise()!=null?cgProject.getCooEnterprise():"",DateUtil.dateToString(cgProject.getProjApproTime()),
							cgProject.getProjectOrigin()!=null?cgProject.getProjectOrigin():"",cgProject.getProjectName()!=null?cgProject.getProjectName():"",Float.toString(cgProject.getFunds()),Float.toString(cgProject.getKeYanFen()),
							Float.toString(cgProject.getKeYanFenReword()),Float.toString(cgProject.getReword()),cgProject.getComment()!=null?cgProject.getComment():""};
					for(int i =0;i<title.length;i++){
						System.out.println("-----------"+content[i]);
						Label label1 = new Label(i, count, content[i]);
						sheet.addCell(label1);

					}
					count++;
				}
			}
			
			/**
			 * 论文
			 */
			if(achievementType.equals("lw")){
				QueryResult<CgThesis> queryResult = chengguoService.searchCgThesisByDate(college, department, postion, education,
						teacherId, cataloge, DateUtil.stringToDate(getStartTime()), DateUtil.stringToDate(getEndTime()),-1,-1);
				List<CgThesis> cgthesislist= queryResult.getResultlist();
				String[] title = {"序号","作者","论文名","期刊名","发表年月","SCI、EI、SSCI、 ISTP等及相当级 别期刊（Y/N）","是否核心 期刊（Y/N）","科研分值",
						"科研分酬金(每分折算4.6元,单位:元)","奖励酬金(元)","备注"};
				
				for(int i=0;i<title.length;i++){
					Label label = new Label(i,0,title[i]);
					sheet.addCell(label);
				}//目录赋值
				Iterator<CgThesis> cgThesisIterator = cgthesislist.iterator();
				while(cgThesisIterator.hasNext()){ 
					CgThesis cgThesis = cgThesisIterator.next();
					String[] content = {Integer.toString(count),cgThesis.getTeacherName()!=null?cgThesis.getTeacherName():"",cgThesis.getThesisTitle(),cgThesis.getKanWu()!=null?cgThesis.getKanWu():"",DateUtil.dateToString(cgThesis.getPublishDate()),
							cgThesis.getSuoyin()!=null?cgThesis.getSuoyin():"",cgThesis.getGrade()!=null?cgThesis.getGrade():"否",Float.toString(cgThesis.getKeYanFen()),Float.toString(cgThesis.getKeYanFenReword()),Float.toString(cgThesis.getReword()),cgThesis.getComment()!=null?cgThesis.getComment():""};
					for(int i =0;i<title.length;i++){
						Label label1 = new Label(i, count, content[i]);
						sheet.addCell(label1);
					}
					count++;
				}
			}
			
			/**
			 * 学术会议
			 */
			if(achievementType.equals("xshy")){
				Map<Teacher, List<CgConference>> map = chengguoService.searchCgConferenceByDate(college, department, postion, education, teacherId, cataloge, DateUtil.stringToDate(startTime), DateUtil.stringToDate(endTime));
				List<CgConference> cgconference = new ArrayList<CgConference>();
				String[] title = {"序号","学会会议名称","教师编号","发表年月"};
				for(Teacher teacher:map.keySet()){
					Iterator it = map.get(teacher).iterator();
					if(it.hasNext()){
						cgconference.add((CgConference) it.next());
					}
				}
				for(int i=0;i<title.length;i++){
					Label label = new Label(i,0,title[i]);
					sheet.addCell(label);
				}
				Iterator<CgConference> conference = cgconference.iterator();
				while(conference.hasNext()){ 
					CgConference th = conference.next();
					String[] content = {Integer.toString(count),th.getConferenceName(),th.getTeacherId(),th.getConferenceDate().toString()};
					for(int i =0;i<title.length;i++){
						Label label1 = new Label(i, count, content[i]);
						sheet.addCell(label1);
					}
					count++;
				}
			}
			
			/**
			 * 科技创新
			 */
			if(achievementType.equals("zdxskjcx")){
				Map<Teacher, List<CgInnovate>> map = chengguoService.searchCgInnovateByDate(college, department, postion, education, teacherId, cataloge, DateUtil.stringToDate(startTime), DateUtil.stringToDate(endTime));
				List<CgInnovate> cginnovate = new ArrayList<CgInnovate>();
				String[] title = {"序号","活动编号","活动名称","指导教师编号","项目编号","项目名称","发表年月"};
				for(Teacher teacher:map.keySet()){
					Iterator it = map.get(teacher).iterator();
					if(it.hasNext()){
						cginnovate.add((CgInnovate) it.next());
					}
				}
				for(int i=0;i<title.length;i++){
					Label label = new Label(i,0,title[i]);
					sheet.addCell(label);
				}
				Iterator<CgInnovate> innovate = cginnovate.iterator();
				while(innovate.hasNext()){ 
					CgInnovate th = innovate.next();
					String[] content = {Integer.toString(count),th.getInnovateId()!=null?th.getInnovateId():"",th.getInnovateName()!=null?th.getInnovateName():"",th.getTeacherId()!=null?th.getTeacherId():"",th.getItemId()!=null?th.getItemId():"",
							th.getItemName()!=null?th.getItemName():"",DateUtil.dateToString(th.getDate())};
					for(int i =0;i<title.length;i++){
						Label label1 = new Label(i, count, content[i]);
						sheet.addCell(label1);
					}
					count++;
				}
			}
			
			/**
			 * 专利
			 */
			if(achievementType.equals("zl")){
				QueryResult<CgPatent> queryResult = chengguoService.searchCgPatentByDate(college, department, postion, education,
						teacherId, cataloge, DateUtil.stringToDate(getStartTime()), DateUtil.stringToDate(getEndTime()),-1,-1);
				List<CgPatent> cgPatentlist= queryResult.getResultlist();
				String[] title = {"序号","负责人","专利名称","获得证书时间","专利类别（发明/实用新型/外观设计/软件开发）",
						"2009年科研分值","科研分酬金(每分折算4.6元,单位:元)","奖励酬金(元)","备 注"};
				for(int i=0;i<title.length;i++){
					Label label = new Label(i,0,title[i]);
					sheet.addCell(label);
				}//目录赋值
				Iterator<CgPatent> cgPatentIterator = cgPatentlist.iterator();
				while(cgPatentIterator.hasNext()){ 
					CgPatent cgPatent = cgPatentIterator.next();
					String[] content = {Integer.toString(count),cgPatent.getFuZeRen()!=null?cgPatent.getFuZeRen():"",cgPatent.getPatentTitle()!=null?cgPatent.getPatentTitle():"",DateUtil.dateToString(cgPatent.getObtainCertificateTime()),
							cgPatent.getPatentType()!=null?cgPatent.getPatentType():"",Float.toString(cgPatent.getKeYanFen()),Float.toString(cgPatent.getKeYanFenReword()),Float.toString(cgPatent.getReword()),cgPatent.getComment()!=null?cgPatent.getComment():""};
					for(int i =0;i<title.length;i++){
						Label label1 = new Label(i, count, content[i]);
						sheet.addCell(label1);
					}
					count++;
				}
			}
			
			/**
			 * 专著
			 */
			if(achievementType.equals("zz")){
				QueryResult<CgTreatise> queryResult = chengguoService.searchCgTreatiseByDate(college, department, postion, education,
						teacherId, cataloge, DateUtil.stringToDate(getStartTime()), DateUtil.stringToDate(getEndTime()),-1,-1);
				List<CgTreatise> cgTreatiselist = queryResult.getResultlist();
				String[] title = {"序号","姓名","主编/副主编/编委/编写","专著/教材名称","出版社名","出版社层次","出版时间","本年度科研分值","科研分酬金(每分折算4.6元,单位:元)","奖励酬金(元)","备注"};				
				for(int i=0;i<title.length;i++){
					Label label = new Label(i,0,title[i]);
					sheet.addCell(label);
				}
				Iterator<CgTreatise> cgTreatiseIterator = cgTreatiselist.iterator();
				while(cgTreatiseIterator.hasNext()){ 
					CgTreatise cgTreatise = cgTreatiseIterator.next();
					String[] content = {Integer.toString(count),cgTreatise.getTeacherName()!=null?cgTreatise.getTeacherName():"",cgTreatise.getEditorRole()!=null?cgTreatise.getEditorRole():"",cgTreatise.getTreatiseTitle()!=null?cgTreatise.getTreatiseTitle():"",
							cgTreatise.getPublishDw()!=null?cgTreatise.getPublishDw():"",cgTreatise.getPressLevel()!=null?cgTreatise.getPressLevel():"",DateUtil.dateToString(cgTreatise.getPublishDate()),Float.toString(cgTreatise.getKeYanFen()),Float.toString(cgTreatise.getKeYanFenReword()),
							Float.toString(cgTreatise.getReword()),cgTreatise.getComment()!=null?cgTreatise.getComment():""};
					for(int i =0;i<title.length;i++){
						Label label1 = new Label(i, count, content[i]);
						sheet.addCell(label1);
					}
					count++;
				}
			}
			book.write();
			book.close();
		} catch (Exception e) {
			System.out.println("生成报表出错啦");
			e.printStackTrace();
		}
	}
	
	public InputStream getTargetFile() throws Exception{
		WritExcel();
		//InputStream in=ServletActionContext.getServletContext().getResourceAsStream("/WEB-INF/download/result.xls");
		return ServletActionContext.getServletContext().getResourceAsStream("/WEB-INF/download/result.xls");
	}
	
	@Override
	public String execute() throws Exception {
		
		return "success";
	}
	
}
